#!/usr/bin/env python
# coding=utf-8
__author__ = 'vavava'
if __name__ == '__main__':
    """ for file debug"""
    import sys,os
    sys.path.insert(0,os.path.join( os.getcwd(), '..' ))

from vavava.dbhelper import _dbhelper
from . import types_track
import time

class TrackingStatus:
    def __init__(self,datalist=[]):
        self.shipment_iD     = datalist[0]
        self.shipment_name   = datalist[1]
        self.shipment_status = datalist[2]
        self.create_time     = datalist[3]
        self.modify_time     = datalist[4]


ITEM_RETRY_OUT = 10
class Tracking(_dbhelper):
    def __init__(self,db_conn=None,log=None):
        _dbhelper.__init__(self,db_conn,log)

    def __del__(self):
        if self.cursor:
            self.cursor.close()

    def init_db_cursor(self,conn):
        self._init_cursor(conn)

    def sp_insert_new_item(self,_Name,_TrackTime,_Description,_Location):
        try:
            self.conn.begin()
            track_time = _TrackTime.strftime("%Y-%m-%d %H:%M:%S")
            result = self.cursor.callproc( "sp_insert_tracking_item",
                           [ _Name, track_time, _Description, _Location ] )
            self.conn.commit()
            res = 0
            n = self.cursor.execute("select @result_")
            res = self.cursor.fetchone()
            if res:
                return res[0]
            else:
                return res
        except Exception as e:
            self.log.exception(e)
            self.conn.rollback()

    def sp_update_item_status(self,_Name,retry_timeout=True):
        try:
            self.conn.begin()
            flag = 0
            if retry_timeout:
                flag = 1
            result = self.cursor.callproc( "sp_update_tracking_item_status", [ _Name, flag ] )
            self.conn.commit()
            res = 0
            n = self.cursor.execute("select @timeout_count_")
            res = self.cursor.fetchone()
            return res
        except Exception as e:
            self.log.exception(e)
            self.conn.rollback()

    def get_tracking_items(self):
        return self._get_item_list_by_sql(
            r'select * from tracking_status where shipment_status >= 0 and shipment_status < %d' % ITEM_RETRY_OUT
        )

    def get_delivered_items(self):
        return self._get_item_list_by_sql(
            r'select * from tracking_status where shipment_status = -1'
        )

    def get_timeout_items(self):
        return self._get_item_list_by_sql(
            r'select * from tracking_status where shipment_status = -2 or shipment_status >= %d' % ITEM_RETRY_OUT
        )

    def item_get_top10(self,item):
        bt=time.clock()
        sql = r"select * from tracking_data where Name = '%s' order by TrackTime desc limit 10" % item
        n = self.cursor.execute(sql)
        re_dict={}
        for i in range(n):
            db_data = self.cursor.fetchone()
            dbitem = types_track.TrackItem.GetInstance(db_data)
            re_dict[dbitem] = ""
        interval = time.clock()-bt
        self.log.debug("interval:item_get_top10(%s,%d) %f" % (item,len(re_dict),interval) )
        return re_dict

    def _get_item_list_by_sql(self,sql):
        bt=time.clock()
        n = self.cursor.execute(sql)
        res = self.cursor.fetchall()
        res_dict = {}
        for each in res:
            if len(each) > 2:
                ts = TrackingStatus(each)
                res_dict[ts.shipment_name] = ts
            else:
                self.log.error( "result:%d" % len(each) )
        interval = time.clock()-bt
        self.log.debug("interval:_get_item_list_by_sql %f" % interval)
        return res_dict

    def sp_log(self,msg,remark=""):
        try:
            result = self.cursor.callproc( "_log", [ msg,remark ] )
        except Exception as e:
            self.log.exception(e)

    def sp_init_test_data(self):
        try:
            result = self.cursor.callproc( "sp_init_test_data", [ ] )
        except Exception as e:
            self.log.exception(e)



# ########################################################################

def test1(conn,log):
    import time
    from vavava.util import FormatTimeStr

    sp = Tracking(conn,log)

    #sp.sp_init_test_data()

    for i in range(10):
        for j in range(14):
            ts = FormatTimeStr.short( time.localtime(time.time()) )
            print(ts)
            result = sp.sp_insert_item( "name%d"%i,ts,"desc%d"%j,"location" )
            print(result)
            #time.sleep(0.2)
    res_list = sp.get_status()
    for each in res_list:
        print(each.shipment_name)

def test2(conn,log):
    table = Tracking(db_conn=conn,log=log)
    res = table.item_get_top5( 'MHB0019387' )
    for each in res:
        log.info(each)

if __name__ == '__main__':
    import vavava.util
    log = vavava.util.initlog("./log/table_tracking.log")

    import MySQLdb,time
    from DBUtils import PooledDB

    dbpool = PooledDB.PooledDB(
        MySQLdb,11,11,11,22,False,
        host='localhost',user='root',passwd='root',db='tracking',
        charset='utf8' )
    conn = dbpool.connection()

    test2(conn,log)














